﻿DECLARE	@vSQL NVARCHAR(MAX)
,	@pDatabaseName	sysname = 'CascadingDataViewerTest'
,	@pSchemaName	sysname = 'dbo'
,	@pTableName		sysname = 'SelfReference';
SET		@vSQL = '
SELECT	i.name as parent_unique_key,fk.name as child_foreign_key,c.[name] AS ParentColumn,fkcc.[name] AS ChildColumn
FROM	[@pDatabaseName].[sys].[tables] t 
INNER	JOIN [@pDatabaseName].[sys].[foreign_keys] fk
ON		t.object_id = fk.parent_object_id
LEFT	OUTER JOIN [@pDatabaseName].[sys].[indexes] i
ON		fk.referenced_object_id = i.[object_id]
AND		fk.key_index_id = i.[index_id]
INNER	JOIN [@pDatabaseName].[sys].[index_columns] ic
ON		i.[index_id] = ic.[index_id]
AND		i.[object_id] = ic.[object_id]
INNER	JOIN [@pDatabaseName].[sys].[columns] c 
ON		ic.[object_id] = c.[object_id]
AND		ic.[column_id] = c.[column_id]
INNER	JOIN [@pDatabaseName].[sys].[foreign_key_columns] fkc
ON		fk.[object_id] = fkc.constraint_object_id
INNER	JOIN [@pDatabaseName].[sys].[columns] fkcc
ON		fkc.[parent_column_id] = fkcc.column_id
AND		fkc.[parent_object_id] = fkcc.[object_id]
WHERE	t.[name] = ''@pTableName''
AND		t.[type] = ''U''
AND		OBJECT_SCHEMA_NAME(t.[object_id],DB_ID(''@pDatabasename'')) = ''@pSchemaname''
AND		fk.parent_object_id = fk.referenced_object_id
'
SET		@vSQL = REPLACE(@vSQL,'@pDatabaseName',@pDatabasename);
SET		@vSQL = REPLACE(@vSQL,'@pSchemaName',@pSchemaName);
SET		@vSQL = REPLACE(@vSQL,'@pTableName',@pTableName);
EXEC	sp_executesql @vSQL;
SELECT	@@ROWCOUNT